﻿Imports Microsoft.Office.Interop

Public Class ExcelTools
    Public StrFile As String = ModGlobals.StrFilePath
    Public StrLogFile As String = ModGlobals.StrLogFile
    Public ExWb As Excel.Workbook
    Public ExSheet As Excel.Worksheet
    Public ExRange As Excel.Range
    Public ExApp As Excel.Application
    Dim myConnection As System.Data.OleDb.OleDbConnection
    Dim excelDataSet As System.Data.DataSet
    Dim excelAdapter As System.Data.OleDb.OleDbDataAdapter
    Public Function OpenExcel() As Excel.Worksheet
        ' Create new Application.
        Dim exApp = New Excel.Application
        Dim exWb As Excel.Workbook
        Dim exSheet As Excel.Worksheet
        'Dim exRange as Excel.Range
        exWb = exApp.Workbooks.Open(StrFile)
        exSheet = DirectCast(exWb.Worksheets(1), Excel.Worksheet)
        Return exSheet
    End Function
    Public Sub Save()
        ExWb.Save()
        'exWb.
    End Sub
    Public Sub Close()
        ExWb.Save()
        'exRange = Nothing
        ExSheet = Nothing
        ExWb = Nothing
        ExApp.Quit()
        ExApp = Nothing
    End Sub
    Public Function LoadData() As DataTable
        Dim myDataTable As DataTable
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFilePath & ";Extended Properties=""Excel 8.0;HDR=YES;""")
        Try
            ExcelAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            ExcelAdapter.TableMappings.Add("Table", "Excel Data")
            ExcelDataSet = New System.Data.DataSet
            ExcelAdapter.Fill(ExcelDataSet)
            myDataTable = ExcelDataSet.Tables(0)
            MyConnection.Close()
            Return myDataTable
        Catch ex As Exception
            MessageBox.Show("Error: " + ex.ToString, "Importing Excel", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function
    Public Sub Export2Excel()
        Dim exApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        'xlApp = New Excel.ApplicationClass
        xlWorkBook = ExApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To NetGui.DataGridView1.RowCount - 2
            For j = 0 To NetGui.DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    NetGui.DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs(strPath & "PingDNSRep.xlsx")
        xlWorkBook.Close()
        ExApp.Quit()

        releaseObject(ExApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file " & strPath & "PingDNSRep.xlsx")
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Public Function RecordCount() As Integer
        ExRange = ExSheet.UsedRange
        RecordCount = ExRange.Rows.Count() - 1
    End Function
End Class
